package ch.ffhs.bscinf2009.organizeanything.data;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

/**
 * @author lir The database service allows to do all CRUD operations on
 *         definitions and instances
 */
public class DatabaseService extends SQLiteOpenHelper {

	// Application context
	Context appContext = null;

	// The Android's default system path of your application database.
	private static String DB_NAME = "organize.db";

	/**
	 * creates a new DB service. Should not be used directly, use the Envirnment
	 * class to gain access
	 * 
	 * @param context
	 *            the contact in which the database operates
	 */
	public DatabaseService(Context context) {
		super(context, DB_NAME, null, 1);
		appContext = context;
	}

	/**
	 * Read all instances of a certain defintion
	 * 
	 * @param def
	 *            the definition to filter for
	 * @return the instances loaded
	 */
	public List<Organizable> readAllEntries(Organizable def) {
		List<Organizable> allEntries = new ArrayList<Organizable>();

		StringBuilder read = new StringBuilder("select rowid, * from ")
				.append(def.getOrganizableName());
		Cursor c = getReadableDatabase().rawQuery(read.toString(), null);
		System.out.println("fetched " + c.getCount() + " rows for query "
				+ read.toString());

		if (c.getCount() == 0) {
			return allEntries;
		}

		c.moveToFirst();
		do {
			Organizable entry = new Organizable();
			entry.setOrganizableName(def.getOrganizableName());

			Organizable.deepCopyProperties(entry, def);

			entry.setRowId(c.getLong(0));
			int i = 1;
			for (AbstractType type : entry.getProperties()) {
				String dataString = c.getString(i);
				String dataPart = null;
				try{
					dataPart = dataString.split(":::")[1];
				}catch(IndexOutOfBoundsException e){
					// it's okay, field was not filled
				}

				if (type instanceof StringType) {
					StringType sType = (StringType) type;
					sType.setData(dataPart);
				}
				if (type instanceof DateType) {
					DateType dType = (DateType) type;
					if(!"01.01.1970".equals(dataPart)){
						dType.setData(dataPart);
					}
				}
				if (type instanceof ValueChooserType) {
					ValueChooserType sType = (ValueChooserType) type;
					sType.setData(dataPart);
				}
				type.setPropertyName(dataString.split(":::")[0]);
				i++;
			}
			System.out.println("read entry of type " + def.getOrganizableName()
					+ ": " + entry);
			allEntries.add(entry);
			c.moveToNext();
		} while (!c.isAfterLast());

		c.close();
		return allEntries;
	}

	/**
	 * Saves a definition by creating a own database table and also storing the
	 * definition information within the definitions table
	 * 
	 * @param def
	 *            the definition to store
	 */
	public void saveDefintion(Organizable def) {
		StringBuilder definitionString = new StringBuilder();
		int i = 0;
		for (AbstractType type : def.getProperties()) {
			definitionString.append("$$$")
					.append(type.getClass().getSimpleName()).append(i)
					.append("::").append(type.getPropertyName()).append("::")
					.append(type.isDescriptorValue() ? "true" : "false")
					.append("::").append(type.buildTypeDefinition());
			i++;
		}

		StringBuilder saveDefinition = new StringBuilder(
				"insert into definitions values ('")
				.append(def.getOrganizableName()).append("', '")
				.append(definitionString).append("')");

		StringBuilder createOrganizableTable = new StringBuilder();
		createOrganizableTable.append(
				"create table if not exists " + def.getOrganizableName())
				.append("(");

		i = 0;
		for (AbstractType type : def.getProperties()) {
			createOrganizableTable.append(type.getClass().getSimpleName())
					.append(i).append(" text, ");
			i++;
		}
		createOrganizableTable
				.deleteCharAt(createOrganizableTable.length() - 1)
				.deleteCharAt(createOrganizableTable.length() - 1);
		createOrganizableTable.append(")");

		System.out.println("save definition for " + def.getOrganizableName());
		getWritableDatabase().beginTransaction();
		try {
			getWritableDatabase().execSQL(
					"delete from definitions where organizableName='"
							+ def.getOrganizableName() + "'");
			getWritableDatabase().execSQL(saveDefinition.toString());
			getWritableDatabase().execSQL(createOrganizableTable.toString());
			getWritableDatabase().setTransactionSuccessful();
		} catch (Exception e) {
			CharSequence text = "An error occured: " + e.getMessage();
			int duration = Toast.LENGTH_LONG;
			Toast.makeText(appContext, text, duration).show();
		} finally {
			getWritableDatabase().endTransaction();
		}
	}

	/**
	 * Reads all available definitions
	 * 
	 * @return all definitions found
	 */
	public List<Organizable> readDefinitions() {
		List<Organizable> allDefinitions = new ArrayList<Organizable>();
		StringBuilder sql = new StringBuilder();
		sql.append("select * from definitions order by organizableName asc");

		Cursor c = getReadableDatabase().rawQuery(sql.toString(), null);

		System.out.println("fetched " + c.getCount() + " rows for query "
				+ sql.toString());
		int i = 0;
		while (i < c.getCount()) {
			c.moveToPosition(i);
			String typeName = c.getString(0);
			String definitionText = c.getString(1);
			String[] types = definitionText.split("\\$\\$\\$");

			Organizable def = new Organizable();
			def.setOrganizableName(typeName);

			List<AbstractType> properties = new ArrayList<AbstractType>();

			for (String type : types) {
				if (type == null || type.isEmpty()) {
					continue;
				}
				String[] typeDef = type.split("::");
				AbstractType typeInstance;
				try {
					String className = getClass().getPackage().getName() + "."
							+ typeDef[0];

					String packageName = className.substring(0,
							className.lastIndexOf("."));
					String typeClassName = className.substring(className
							.lastIndexOf(".") + 1);
					typeClassName = typeClassName.replaceAll("[\\d]", "");
					className = packageName + "." + typeClassName;

					typeInstance = (AbstractType) Class.forName(className)
							.newInstance();

					typeInstance.setPropertyName(typeDef[1]);

					typeInstance.setDescriptorValue(Boolean
							.parseBoolean(typeDef[2]));
					try {
						typeInstance.parseTypeDefinition(typeDef[3]);
					} catch (IndexOutOfBoundsException e) {
						// never mind, this type does not have configuration
					}
					properties.add(typeInstance);
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
			def.setProperties(properties);
			System.out.println("found definition for "
					+ def.getOrganizableName());
			allDefinitions.add(def);
			i++;
		}
		c.close();
		return allDefinitions;
	}

	/**
	 * Removes a definition from the database. Does also remove all instances of
	 * this definition.
	 * 
	 * @param def
	 *            the definition to remove
	 */
	public void removeDefinition(Organizable def) {
		StringBuilder deleteDefinition = new StringBuilder(
				"delete from definitions where organizableName='").append(
				def.getOrganizableName()).append("'");
		StringBuilder dropDefinitionData = new StringBuilder(
				"drop table if exists ").append(def.getOrganizableName());
		getWritableDatabase().beginTransaction();
		try {
			getWritableDatabase().execSQL(deleteDefinition.toString());
			getWritableDatabase().execSQL(dropDefinitionData.toString());
			getWritableDatabase().setTransactionSuccessful();
		} finally {
			getWritableDatabase().endTransaction();
		}
	}

	/**
	 * creates a new instance of a definition in the database
	 * 
	 * @param orgs
	 */
	public void addEntry(Organizable... orgs) {
		getWritableDatabase().beginTransaction();
		try {
			for (Organizable organizable : orgs) {
				StringBuilder addEntry = new StringBuilder("insert into ")
						.append(organizable.getOrganizableName());
				generateValue(organizable, addEntry);
				getWritableDatabase().execSQL(addEntry.toString());
				System.out.println("added one entry for "
						+ organizable.getOrganizableName());
			}
			getWritableDatabase().setTransactionSuccessful();
		} finally {
			getWritableDatabase().endTransaction();
		}
	}

	private void generateValue(Organizable org, StringBuilder addEntry) {
		addEntry.append(" values (\"");
		for (AbstractType type : org.getProperties()) {
			Object data = null;
			if (type instanceof StringType) {
				data = ((StringType) type).getData();
			}
			if (type instanceof DateType) {
				data = ((DateType) type).getData();
			}
			if (type instanceof ValueChooserType) {
				data = ((ValueChooserType) type).getData();
			}
			String propName = type.getPropertyName();
			addEntry.append(propName + ":::" + data).append("\", \"");
		}
		addEntry.deleteCharAt(addEntry.length() - 1)
				.deleteCharAt(addEntry.length() - 1)
				.deleteCharAt(addEntry.length() - 1);
		addEntry.append(")");
	}

	/**
	 * Updates an existing instance with new values
	 * 
	 * @param org
	 *            the updated instance
	 */
	public void updateEntry(Organizable org) {
		StringBuilder updateEntry = new StringBuilder("update ").append(
				org.getOrganizableName()).append(" set ");

		int i = 0;
		for (AbstractType t : org.getProperties()) {
			updateEntry.append(t.getClass().getSimpleName()).append(i)
					.append("=\"").append(genValue(t)).append("\",");
			i++;
		}
		updateEntry.deleteCharAt(updateEntry.length() - 1);

		updateEntry.append(" where rowid=").append(org.rowId);
		getWritableDatabase().beginTransaction();
		try {
			getWritableDatabase().execSQL(updateEntry.toString());
			getWritableDatabase().setTransactionSuccessful();
		} finally {
			getWritableDatabase().endTransaction();
		}
	}

	private Object genValue(AbstractType type) {
		Object data = null;
		if (type instanceof StringType) {
			data = ((StringType) type).getData();
		}
		if (type instanceof DateType) {
			data = ((DateType) type).getData();
		}
		if (type instanceof ValueChooserType) {
			data = ((ValueChooserType) type).getData();
		}
		String propName = type.getPropertyName();
		return propName + ":::" + data;
	}

	/**
	 * Removes instances of a definition
	 * 
	 * @param list
	 *            the instances to delete
	 */
	public void deleteEntry(List<Organizable> list) {
		for (Organizable organizable : list) {
			StringBuilder deleteEntry = new StringBuilder("delete from ")
					.append(organizable.getOrganizableName())
					.append(" where rowid=").append(organizable.getRowId());
			getWritableDatabase().beginTransaction();
			try {
				getWritableDatabase().execSQL(deleteEntry.toString());
				getWritableDatabase().setTransactionSuccessful();
			} finally {
				getWritableDatabase().endTransaction();
			}
		}
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		StringBuilder sql = new StringBuilder();
		System.out.println("create base tables if needed");
		sql.append("create table if not exists definitions(organizableName text, definition text)");
		db.beginTransaction();
		try {
			db.execSQL(sql.toString());
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		System.out.println("base tables created");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// we do not have any upgrade work in the first version yet
	}

}
